package com.groupThree.dao.imp;

import com.groupThree.bean.Courier;
import com.groupThree.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CourierDaoMysql implements com.groupThree.dao.BaseCourierDao {
    //查询快递员总数+今日新注册数
    public static final String SQL_CONSOLE = "SELECT COUNT(C_ID) data_size,COUNT(TO_DAYS(REGISTERTIME)=TO_DAYS(NOW()) OR NULL) data_day FROM COURIER";
    public static final String SQL_FIND_ALL ="SELECT *FROM COURIER";
    //用于分页查询数据库中的快递信息
    public static final String SQL_FIND_LIMIT = "SELECT * FROM COURIER LIMIT ?,?";
    public static final String SQL_FIND_BY_C_PHONE ="SELECT * FROM COURIER WHERE C_PHONE =?";
    public static final String SQL_INSERT = "INSERT INTO COURIER(c_name, c_phone, c_idcard, c_password, registertime ) VALUES (?,?,?,?,NOW())";
    public static final String SQL_UPDATE = "UPDATE COURIER SET c_name =?, c_phone =?, c_idcard =?, c_password =? WHERE C_ID = ?";
    public static final String SQL_DELETE = "DELETE FROM COURIER WHERE C_PHONE=?";
    private static final String SQL_FIND_SAME_C_PHONE = "SELECT * FROM  COURIER WHERE C_PHONE = ?";
    private static final String SQL_FIND_SAME_C_IDCARD = "SELECT * FROM  COURIER WHERE C_IDCARD = ?";
    private static final String SQL_DELETE_C_ID = "DELETE FROM COURIER WHERE C_ID=?";
    private static final String SQL_UPDATE_LOGIN_TIME ="UPDATE COURIER SET logintime = NOW() WHERE C_PHONE = ?" ;
    private static final String SQL_UPDATE_C_DELIVEEIES = "UPDATE COURIER \n" +
            "SET c_deliveries =c_deliveries+1 \n" +
            "WHERE C_PHONE = ?" ;


    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null ;
        try {
            state = conn.prepareStatement(SQL_CONSOLE);

            result = state.executeQuery();
            if (result.next()){
                int data_size = result.getInt("data_size");
                int data_day = result.getInt("data_day");
                Map data1 = new HashMap();
                data1.put("data_size",data_size);
                data1.put("data_day",data_day);
                data.add(data1);

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }

        return data;
    }

    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Courier> data = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null ;
        try {
            if (limit){
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {

                state = conn.prepareStatement(SQL_FIND_ALL);

            }
            result = state.executeQuery();

            while (result.next()){
                int c_id = result.getInt("c_id");
                String c_name = result.getString("c_name");
                String c_phone = result.getString("c_phone");
                String c_idcard = result.getString("c_idcard");
                String c_password = result.getString("c_password");
                int c_deliveries = result.getInt("c_deliveries");
                Timestamp registertime = result.getTimestamp("registertime");
                Timestamp logintime = result.getTimestamp("logintime");
                Courier c = new Courier( c_id,c_name,c_phone, c_idcard, c_password,c_deliveries, registertime, logintime);
                data.add(c);

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    @Override
    public Courier findByC_Phone(String c_phone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null ;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_C_PHONE);
            state.setString(1,c_phone);

            result = state.executeQuery();
            if (result.next()){
                int c_id = result.getInt("c_id");
                String c_name = result.getString("c_name");
                String c_idcard = result.getString("c_idcard");
                String c_password = result.getString("c_password");
                int c_deliveries = result.getInt("c_deliveries");
                Timestamp registertime = result.getTimestamp("registertime");
                Timestamp logintime = result.getTimestamp("logintime");
                Courier c = new Courier( c_id,c_name,c_phone, c_idcard, c_password,c_deliveries, registertime, logintime);
                return c;

            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    @Override
    public boolean insert(Courier c) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null ;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            state.setString(1,c.getC_name());
            state.setString(2,c.getC_phone());
            state.setString(3,c.getC_idcard());
            state.setString(4,c.getC_password());

            return state.executeUpdate()>0?true:false;



        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean update(int c_id, Courier newCourier) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null ;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1,newCourier.getC_name());
            state.setString(2,newCourier.getC_phone());
            state.setString(3,newCourier.getC_idcard());
            state.setString(4,newCourier.getC_password());
            state.setInt(5,newCourier.getC_id());

             return state.executeUpdate()>0?true:false;
      } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean delete(String c_phone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setString(1,c_phone);


            return state.executeUpdate()>0?true:false;



        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean delete(int c_id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        String id = String.valueOf(c_id);
        try {
            state = conn.prepareStatement(SQL_DELETE_C_ID);
            state.setString(1,id);


            return state.executeUpdate()>0?true:false;



        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 存在返回ture
     * @param c_phone
     * @return
     */
    @Override
    public boolean findSameC_Phone(String c_phone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null ;
        try {
            state = conn.prepareStatement(SQL_FIND_SAME_C_PHONE);
            state.setString(1,c_phone);
            result = state.executeQuery();
            if (result.next()){
                return true;
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }

        return false;
    }

    /**
     * 存在返回ture
     * @param c_idcard
     * @return
     */

    @Override
    public boolean findSameC_IDcard(String c_idcard) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null ;
        try {
            state = conn.prepareStatement(SQL_FIND_SAME_C_IDCARD);
            state.setString(1,c_idcard);
            result = state.executeQuery();
            if (result.next()){
                return true;
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }

        return false;
    }

    @Override
    public boolean updateLoginTime(String c_phone) {
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_UPDATE_LOGIN_TIME);
            state.setString(1,  c_phone);
            //执行sql
            return state.executeUpdate()>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }

        return false;
    }

    @Override
    public boolean updateDeliveries(String c_phone) {

        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_UPDATE_C_DELIVEEIES);
            state.setString(1,  c_phone);
            //执行sql
            return state.executeUpdate()>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;

    }
}
